﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.Data.SQLite;
using System.Text.RegularExpressions;

namespace PTCScheduler.EditMenu_Pages
{
    /// <summary>
    /// Interaction logic for UpdateStudent_Page.xaml
    /// </summary>
    public partial class UpdateStudent_Page : Page, ScheduleReport
    {
        //SQLite connection for the main window page
        private SQLiteConnection conn;

        //SQLite data dapter for main window page
        private SQLiteDataAdapter da;

        //SQLite database connection string
        private String connString = SystemQueries.dbConnString();

        //SQLite transaction object
        private SQLiteTransaction transaction;

        //Array to hold the old data for student update.
        private String[] arrOldData = new String[5];

        public UpdateStudent_Page()
        {
            InitializeComponent();

            //Connect to database.
            dbConn();

            //Query results and fill datagrid.
            queryResults();
        }

        //Clear form and refocus.
        private void ClearForm()
        {
            //Clear user interface textboxes.
            txtStudentFName.Clear();
            txtStudentLName.Clear();
            txtStudentID.Clear();
            txtParentEmail.Clear();
            txtParentPhone.Clear();

            //Refocus to student data grid.
            dataGridStudent.Focus();
        }

        //Method to check for valid email.
        public Boolean validEmail(String email)
        {
            if (email.Contains("@") && email.Contains("."))
            {
                return true;
            }
            return false;
        }

        //Method to hide all labels.
        private void HideAllLabels()
        {
            //Visibility.Hidden.
            lblInformationCheck.Visibility = Visibility.Hidden;
            lblStudentSuccess.Visibility = Visibility.Hidden;
        }

        //Handles the update button event which updates student information. 
        private void btnUpdate_Click(object sender, RoutedEventArgs e)
        {
            //Hide all labels by default.
            HideAllLabels();
            string bounds = @"^[a-zA-Z]+$";
            try//Try catch to catch format exceptions.
            {
                //Check to ensure teacher first name and last name is entered.
                if (txtStudentFName.Text == String.Empty || txtStudentLName.Text == String.Empty || txtStudentID.Text == String.Empty)
                {
                    throw new MissingFieldException();
                }
                if(!(txtParentEmail.Text == ""))
                {
                    if(!validEmail(txtParentEmail.Text))
                        throw new FormatException();
                }
                if (Regex.IsMatch(txtStudentFName.Text, bounds) && Regex.IsMatch(txtStudentLName.Text, bounds) && !Regex.IsMatch(txtStudentID.Text, bounds))
                {
                    //Open connection
                    dbConn();

                    //Set the SQLiteTransaction
                    transaction = conn.BeginTransaction();

                    //Create the SQLite command and set its properties
                    SQLiteCommand command = new SQLiteCommand();
                    command.Transaction = transaction;
                    command.CommandText = this.updateStudentString();
                    command.ExecuteNonQuery();

                    //Commit the transaction
                    transaction.Commit();

                    //Display successful transaction message.
                    lblStudentSuccess.Visibility = Visibility.Visible;

                    //Clear form.
                    ClearForm();

                    //Update datagridview.
                    RefreshDataGridView();
                }
                else
                {
                    throw new FormatException();
                }
            }
            catch (Exception ex)
            {
                if (ex is SQLiteException)
                {
                    //Display provide all information or invalid information label.
                    lblInformationCheck.Visibility = Visibility.Visible;
                }
                if (ex is FormatException)
                {
                    //Display warning label.
                    lblInformationCheck.Visibility = Visibility.Visible;
                }
                if (ex is MissingFieldException)
                {
                    //Display missing field label.
                    lblInformationCheck.Visibility = Visibility.Visible;
                }
            }
            finally
            {
                //Close and dispose of connection.
                conn.Close();
                conn.Dispose();
            }//End Try-Catch
        }

        //Update student string.
        private String updateStudentString()
        {
            return @"UPDATE students " +
                    "SET fname = '" + txtStudentFName.Text + "', lname = '" + txtStudentLName.Text + "', " +
                            "studentID = " + Convert.ToInt32(txtStudentID.Text) + ", parentEmail = '" + txtParentEmail.Text + "', " +
                            "parentPhone = '" + txtParentPhone.Text + "' " +
                     "WHERE fname = '" + arrOldData[0] + "' AND lname = '" + arrOldData[1] + "' AND studentID = " + Convert.ToInt32(arrOldData[2]) + " " +
                     "AND parentEmail = '" + arrOldData[3] + "' AND parentPhone = '" + arrOldData[4] + "' ";
        }

        //Input data from datagridview into textboxes to be updated.
        private void dataGridStudent_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
                //Create data grid view row object.
                DataRowView row = (DataRowView)dataGridStudent.SelectedItems[0];

                //Retrieve column values and insert into textboxes.
                setTextBoxes(row);
        }

        //Method to set textboxes to values in data row view.
        private void setTextBoxes(DataRowView row)
        {
            //Set text properties.
            txtStudentFName.Text = row["First Name"].ToString();
            txtStudentLName.Text = row["Last Name"].ToString();
            txtStudentID.Text = row["Student ID"].ToString();
            txtParentPhone.Text = row["Parent Phone"].ToString();
            txtParentEmail.Text = row["Parent Email"].ToString();

            //Set the array old date to old values
            arrOldData[0] = row["First Name"].ToString();
            arrOldData[1] = row["Last Name"].ToString();
            arrOldData[2] = row["Student ID"].ToString();
            arrOldData[3] = row["Parent Email"].ToString();
            arrOldData[4] = row["Parent Phone"].ToString();
        }

        //Connect to the database.
        public void dbConn()
        {
            //Create connection.
            conn = new SQLiteConnection(connString);

            //Open connection
            conn.Open();
        }

        //Query the results and fill the datagrid.
        public void queryResults()
        {
            //Retrieve the student table query string from the SystemQueries class. 
            String sql = SystemQueries.studentTableQueryString();

            //Create dataset to fill with the results of the query. 
            DataSet ds = new DataSet(); //System.Data assembly

            //User SQLiteDataAdapter to retrieve results. 
            da = new SQLiteDataAdapter(sql, connString);

            //Fill the dataset with the results. 
            da.Fill(ds);

            //If there are no records then prompt user that there are no scheduled
            //conferences for today. Otherwise just display records. 
            if (ds.Tables[0].Rows.Count == 0)
            {
                lblNoStudents.Visibility = Visibility.Visible;
            }
            else
            {
                lblNoStudents.Visibility = Visibility.Hidden;
            }

            //Display results in datagrid view. 
            dataGridStudent.ItemsSource = ds.Tables[0].DefaultView;

            //Close database connection.
            conn.Close();
        }

        //Method to refresh database with new results.
        private void RefreshDataGridView()
        {
            //Unregister selection changed event to fill data grid.
            dataGridStudent.SelectionChanged -= dataGridStudent_SelectionChanged;

            //Open connection and update dataview.
            dbConn();
            queryResults();

            //Register selection changed event to handle selection changes.
            dataGridStudent.SelectionChanged += dataGridStudent_SelectionChanged;
        }
    }
}
